# -*- coding: utf-8 -*-

import pymysql
import pandas as pd
import csv
import re
import numpy as np
from datetime import datetime

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()


sql1 = """ \
       SELECT shop, \
              product_BianMa, \
              SUM(Product_num), \
              SUM(Product_price), \
              SUM(yh_pingtai) \
       from niandu_day \
       where month (day)=10 \
         and \
           (order_zt="完成" \
          OR order_zt="等待出库" \
          OR order_zt="等待确认收货" \
          OR order_zt="已签收" \
          OR order_zt="待配货" \
          OR order_zt="待发货" \
          OR order_zt="交易成功" \
          OR order_zt="已完成" \
          OR order_zt="卖家已发货，等待买家确认" \
          OR order_zt="买家已付款，等待卖家发货" \
          OR order_zt="已发货" \
          OR order_zt="调度中" \
          OR order_zt="已收货" \
          OR order_zt="已发货未签收" \
          OR order_zt="已发货，待签收" \
          OR order_zt="(锁定)等待确认收货" \
          OR order_zt="已发货，待收货" \
          OR order_zt="(锁定)等待确认收货" \
          OR order_zt="部分发货" \
          OR order_zt="买家已付款,等待卖家发货" \
          OR order_zt="卖家已发货" \
          OR order_zt="买家已付款" \
          OR order_zt="发货即将超时" \
          OR order_zt="卖家部分发货" \
          OR order_zt="待买家收货" \
          OR order_zt="待卖家发货" \
          OR order_zt="部分发货中") \
         AND order_sh != "退款成功" \
         AND order_sh != "售后完成" \
         AND order_sh != "退款完成" \
         AND order_sh != "退货退款完成 " \
       group by shop, product_BianMa
     """

cursor.execute(sql1)
result1 = cursor.fetchall()
xs_xs = pd.DataFrame(list(result1))
xs_xs.columns = ['店铺', '编码', '商品数量', '应付', '平台优惠']
xs_xs.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月利润测算明细_线上.xlsx')

sql11 = """ \
        SELECT * \
        from doudian_day \
        where month (day)=10 \
          and dianzhuguo="电煮锅赠品" \
          and \
            ( order_zt="已签收" \
           OR order_zt="待发货" \
           OR order_zt="已完成" \
           OR order_zt="已发货" \
           OR order_zt="已支付") \
          AND order_sh != "退款成功" \
          AND order_sh != "售后完成" \
          AND order_sh != "退款完成" \
          AND order_sh != "已全额退款" \

      """
cursor.execute(sql11)
result11 = cursor.fetchall()
dg = pd.DataFrame(list(result11))
dg.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月锅.xlsx')

sql2 = """ \
       select product_BianMa, \  
              SUM(Product_num), \
              SUM(Product_price), \
              SUM(yh_pingtai), \
              SUM(yh_daren) \
       from doudian_day \
       where month (day)=10 \
         and \
           ( order_zt="已签收" \
          OR order_zt="待发货" \
          OR order_zt="已完成" \
          OR order_zt="已发货" \
          OR order_zt="已支付") \
         AND order_sh != "退款成功" \
         AND order_sh != "售后完成" \
         AND order_sh != "退款完成" \
         AND order_sh != "已全额退款" \
       group by product_BianMa \

     """

sql22 = """ \
        select product_BianMa, \
               Product_dj, \
               SUM(Product_num), \
               SUM(Product_price), \
               SUM(yh_pingtai) \
        from doudian_day \
        where month (day)=10 \
          and \
            ( order_zt="已签收" \
           OR order_zt="待发货" \
           OR order_zt="已完成" \
           OR order_zt="已发货" \
           OR order_zt="已支付") \
          AND order_sh != "退款成功" \
          AND order_sh != "售后完成" \
          AND order_sh != "退款完成" \
          AND order_sh != "已全额退款" \
        group by product_BianMa, Product_dj \

      """

cursor.execute(sql2)
result2 = cursor.fetchall()

jg = pd.DataFrame(list(result2))
jg.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月抖音编码价格.xlsx')

cursor.execute(sql22)
result22 = cursor.fetchall()

dj = pd.DataFrame(list(result22))
dj.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月抖音编码单价.xlsx')

